<?php

/**
 * @apiVersion 1.0.0
 * @api {POST} /backstage/ClientUser/userSearch 0001根据葵码搜索用户OK
 * @apiDescription 根据葵码搜索用户
 * @apiGroup user_manage
 * @apiPermission
 * @apiParam {string} [xrkCode] 葵码
 * @apiParamExample {json} 请求示例:
 * {
 *      "xrkCode": '比001',
 * }

 * @apiSuccess  {Int} code 状态
 * @apiSuccess  {Object} data 元数据
 * @apiSuccess  {String} msg 错误说明
 * @apiSuccessExample {json} 响应示例:
    {
        "code": 1,
        "data": {
            addTime: "2021-04-26 20:21:12"  //注册时间
            apliayPassword: "Kan996816"     //支付宝登陆密码
            apliayPayPassword: "996816"     //支付宝支付密码
            apliayUsername: "18252186591"   //支付宝账号
            cMobile: "15895289309"  //常用手机号
            card: "320323197605274424"  //身份证
            checkManger: null  //审核员ID
            checkStatusText: "未知"     //检查状态
            checkTime: null   //审核时间
            depositRealname: "阚玲"     //提现真实姓名
            depositType: "1"      //提现方式： 1支付宝 2微信
            depositUsername: "15895289309"    //提现账号
            fopenID: null
            funionID: null
            judge: 0
            mobile: "18252186591"     //手机号
            nickname: "阚玲"    //昵称
            openID: null  //openID
            password: "e1419b223ac8b4a6da2ede241ddca8cf"
            payPassword: "e1419b223ac8b4a6da2ede241ddca8cf"
            photo: "https://laike-1257904924.cos-website.ap-shanghai.myqcloud.com/2021-04-17/c79048424766793e218fbb056a2a2c2d.jpg"
            platformID: 1     //平台ID
            serialNumber: null
            sex: "女"      //性别
            status: 0     //状态
            statusCheck: -1
            statusText: "冻结"      //状态标识
            trueName: "阚玲"    //真实姓名
            unionID: null   开发平台ID
            updataTime: "2021-04-26 20:21:12"
            userID: 236   //用户ID
            version: null
            wechatPassword: "996816"      //微信登录密码
            wechatPayPassword: "518668"  //微信支付密码
            wechatUsername: "Kan996816"   //微信账号
            xrkCode: "bt236"      //葵码
        }
    }

 * //操作未成功提示信息
 * {
 *      "code":0,
 *      "msg" :"错误说明"
 * }
 */

namespace app\backstage\controller;


use app\model\FreshOrder;
use app\model\User as model;
use app\model\FreshUserAward;
use app\model\FreshUserMoneyLog;
use app\model\User;
use app\model\UserProgram;
use app\util\ReturnCode;
use app\util\Tools;
use Cassandra\Varint;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use think\Exception;

class ClientUser extends Base {

    public function index(){
        $page = $this->request->get('page', 1);
        $size = $this->request->get('size', config('apiAdmin.ADMIN_LIST_DEFAULT'));
        $postData = $this->request->get();
        $where = function($query)use($postData){
            if(isset($postData['status'])){
                $query->where('u.status', intval($postData['status']));
            }
            if(isset($postData['mobile'])&& $postData['mobile']){
                $query->where('u.mobile', $postData['mobile']);
            }
            if(isset($postData['keyword'])&& $postData['keyword']){
                $query->where('u.nickname|u.trueName',  'like','%'.$postData['keyword'].'%');
            }
            //keywordMobile
            if(isset($postData['keywordMobile'])&& $postData['keywordMobile']){
                $query->where('u.mobile',  'like','%'.$postData['keywordMobile'].'%');
            }
            if(isset($postData['xrkCode'])&& $postData['xrkCode']){
                $query->where('u.xrkCode', 'like', '%'.$postData['xrkCode'].'%');
            }
            if(isset($postData['card'])){
                $query->where('u.card', $postData['card']);
            }

            $platformID = $this->userPlatform();
            if($platformID == 0){
                if(isset($postData['platformID'])   && $postData['platformID']){
                    $query->where('u.platformID', $postData['platformID']);
                }
            }else{
                $query->where('u.platformID', $platformID);
            }

            if(isset($postData['isAllot']) && $postData['isAllot']){
                $query->where('u.isAllot', $postData['isAllot']);
            }
            if(isset($postData['apliayUsername'])){
                $query->where('u.apliayUsername', $postData['apliayUsername']);
            }
            if(isset($postData['wechatUsername'])){
                $query->where('u.wechatUsername', $postData['wechatUsername']);
            }
            if(isset($postData['beginTime']) && $postData['beginTime']){
                $query->where('u.addTime', '>=', strtotime($postData['beginTime']));
            }
            if(isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('u.addTime', '<=', $endTime);
            }
            if(isset($postData['beginTime']) && $postData['beginTime'] && isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('u.addTime', [ '>=' , strtotime($postData['beginTime'])],['<=', $endTime], 'and');
            }

        };

        if(isset($postData['orderBy'])){
            $orderBy = $postData['orderBy'].' desc';
        }else{
            $orderBy = 'u.userID desc';
        }

        if(isset($postData['isDesc'])){
            if($postData['isDesc'] == 1){
                $orderBy = $orderBy." desc";
            }else{
                $orderBy = $orderBy." asc";
            }
        }else{
            $orderBy = $orderBy." desc";
        }

        $list = model::alias('u')
            ->with('platformData')
        ->where($where)
        ->order($orderBy)
        ->limit(( $page - 1 ) * $size , $size)
        ->select();

        if($list){
            $list->append(['statusText']);
            $list->append(['checkStatusText']);
            $list->append(['isAllotText']);
        }

        foreach($list as $key => $var){
            $var->data('trueName', $var['trueName'] ? : '');
            $var->data('card', $var['card'] ? : '');
            $var->data('nickname', $var['nickname'] ? emoji_decode($var['nickname']): '');
            $allot = Db::name('user_allot')->where('userID', $var['userID'])->find();
            if(isset($allot['userID'])){
                $admin_user = Db::name('fresh_admin_user')->where('id', $allot['opMid'])->find();
                $var->data('opNickname', $admin_user['username']);
            }else{
                $var->data('opNickname', '/');
            }
        }

        $count =   model::alias('u')
        ->where($where)->count();

        return $this->buildSuccess([
            'count'=>$count,
            'list'=>$list
        ]);
    }


    public function add(){

        $postData = $this->request->post();
        if(!isset($postData['mobile']))  return $this->buildFailed(ReturnCode::INVALID, '手机号不能为空');
        $result = \app\model\User::get(['mobile' => $postData['mobile']]);
        if(isset($result['userID'])) return $this->buildFailed(ReturnCode::INVALID, '该手机号码已注册');
        if(!isset($postData['sex']))  return $this->buildFailed(ReturnCode::INVALID, '性别不能为空');
        if(!isset($postData['nickname']))  return $this->buildFailed(ReturnCode::INVALID, '昵称不能为空');
        if(!isset($postData['trueName']))  return $this->buildFailed(ReturnCode::INVALID, '真是姓名不能为空');
        if(!isset($postData['card']))  return $this->buildFailed(ReturnCode::INVALID, '身份证号码不能为空');
        $result = \app\model\User::get(['card' => $postData['card']]);
        if(isset($result['userID'])) return $this->buildFailed(ReturnCode::INVALID, '该身份证号码已注册');
        if(!isset($postData['apliayUsername']))  return $this->buildFailed(ReturnCode::INVALID, '支付宝账号不能为空');
        if(!isset($postData['apliayPassword']))  return $this->buildFailed(ReturnCode::INVALID, '支付宝账号登录密码不能为空');
        if(!isset($postData['apliayPayPassword']))  return $this->buildFailed(ReturnCode::INVALID, '支付宝账号支付密码不能为空');
        $result = \app\model\User::get(['apliayUsername' => $postData['apliayUsername']]);
        if(isset($result['userID'])) return $this->buildFailed(ReturnCode::INVALID, '该支付宝账号已注册');
        if(!isset($postData['wechatUsername']))  return $this->buildFailed(ReturnCode::INVALID, '微信账号不能为空');
        if(!isset($postData['wechatPassword']))  return $this->buildFailed(ReturnCode::INVALID, '微信账号登录密码不能为空');
        if(!isset($postData['wechatPayPassword']))  return $this->buildFailed(ReturnCode::INVALID, '微信账号支付密码不能为空');
        $result = \app\model\User::get(['wechatUsername' => $postData['wechatUsername']]);
        if(isset($result['userID'])) return $this->buildFailed(ReturnCode::INVALID, '该微信账号已注册');
        if(!isset($postData['depositUsername']))  return $this->buildFailed(ReturnCode::INVALID, '收款账号不能为空');
        if(!isset($postData['depositType']))  return $this->buildFailed(ReturnCode::INVALID, '收款类型必须选择');
        if(!isset($postData['platformID']))  return $this->buildFailed(ReturnCode::INVALID, '平台id不能为空');
        if(!isset($postData['xrkCode']))  return $this->buildFailed(ReturnCode::INVALID, '葵码必须填写');
        $result = \app\model\User::get(['xrkCode' => $postData['xrkCode']]);
        if(isset($result['userID'])) return $this->buildFailed(ReturnCode::INVALID, '该葵码已注册');
        if(!isset($postData['sex']))  return $this->buildFailed(ReturnCode::INVALID, '性别不能为空');
        if(!isset($postData['cMobile']))  return $this->buildFailed(ReturnCode::INVALID, '常用手机号不能为空');
        $postData['photo'] = config('program.backage_logo');
        $postData['addTime'] = time();
        $postData['password'] = md5('123456'."mlkj");
        $postData['payPassword'] = md5('123456'."mlkj");
        $res = \app\model\User::create($postData);
        if ($res === false) {
            return $this->buildFailed(ReturnCode::DB_SAVE_ERROR, '操作失败');
        } else {
            return $this->buildSuccess([]);
        }

    }

    //导入
    public function daoUser(){


        if(empty($_FILES)){
            return $this->buildFailed(ReturnCode::INVALID, '请上传要批量导入的用户资料');
        }
        $inputFileName = $_FILES['file']['tmp_name'];
        $name = $_FILES['file']['name'];
        $arr_name = explode('.', $name);
        $inputFileType = ucfirst(array_pop($arr_name));
        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $num  = 0;
        for($i = 2; $i <= count($sheetData); $i++){
            if(empty($sheetData[$i]['A']) || empty($sheetData[$i]['B']) || empty($sheetData[$i]['C'])
            || empty($sheetData[$i]['D']) || empty($sheetData[$i]['E']) || empty($sheetData[$i]['F'])
            || empty($sheetData[$i]['G']) || empty($sheetData[$i]['H']) || empty($sheetData[$i]['I'])
            || empty($sheetData[$i]['J']) || empty($sheetData[$i]['K']) || empty($sheetData[$i]['L'])
            || empty($sheetData[$i]['M']) || empty($sheetData[$i]['N']) || empty($sheetData[$i]['O'])
                || empty($sheetData[$i]['P'])|| empty($sheetData[$i]['Q'])){
                return $this->buildFailed(ReturnCode::INVALID, "第".Tools::bcCalculate($i,1, '-')."数据不全");
            }

            $mobile = $sheetData[$i]['A'];
            $flag = Db::name('user')->where('mobile', $mobile)->find();
            if(isset($flag['userID'])) return $this->buildFailed(ReturnCode::INVALID, "手机号($mobile)已注册");

            $card = $sheetData[$i]['E'];
            $flag = Db::name('user')->where('card', $card)->find();
            if(isset($flag['userID'])) return $this->buildFailed(ReturnCode::INVALID, "身份证号码($card)已注册");

            $apliayUsername = $sheetData[$i]['F'];
            $flag = Db::name('user')->where('apliayUsername', $apliayUsername)->find();
            if(isset($flag['userID'])) return $this->buildFailed(ReturnCode::INVALID, "支付宝账号($apliayUsername)已注册");

            $wechatUsername = $sheetData[$i]['I'];
            $flag = Db::name('user')->where('wechatUsername', $wechatUsername)->find();
            if(isset($flag['userID'])) return $this->buildFailed(ReturnCode::INVALID, "微信账号($wechatUsername)已注册");

            $xrkCode = $sheetData[$i]['O'];
            $flag = Db::name('user')->where('xrkCode', $xrkCode)->find();
            if(isset($flag['userID'])) return $this->buildFailed(ReturnCode::INVALID, "葵码编号($xrkCode)已绑定");

            $platformID = $sheetData[$i]['N'];
            $flag = Db::name('platform')->where('id', $platformID)->find();
            if(!isset($flag['id'])) return $this->buildFailed(ReturnCode::INVALID, "平台不存在($platformID)已注册");


            $add = [
                'mobile' => $mobile,
                'photo' => config('program.backage_logo'),
                'nickname' => $sheetData[$i]['C'],
                'sex' => $sheetData[$i]['B'],
                'trueName' => $sheetData[$i]['D'],
                'card' => $sheetData[$i]['E'],
                'addTime' => time(),
                'status' => 1,
                'password' => md5('123456'."mlkj"),
                'payPassword' => md5('123456'."mlkj"),
                'apliayUsername' => $sheetData[$i]['F'],
                'apliayPassword' => $sheetData[$i]['G'],
                'apliayPayPassword' => $sheetData[$i]['H'],
                'wechatUsername' => $sheetData[$i]['I'],
                'wechatPassword' => $sheetData[$i]['J'],
                'wechatPayPassword' => $sheetData[$i]['K'],
                'depositUsername' => $sheetData[$i]['L'],
                'depositRealname' => $sheetData[$i]['M'],
                'depositType' => $sheetData[$i]['N'],
                'platformID' => $sheetData[$i]['O'],
                'xrkCode' => $sheetData[$i]['P'],
                'cMobile' => $sheetData[$i]['Q'],
                'statusCheck' => 1,
            ];
            $pass_list = config('pass.key');
            foreach ($pass_list as $item){
                $add[$item] = random(8, 'all');
            }
            $res = \app\model\User::create($add);
            if(false === $res){
                return $this->buildFailed(ReturnCode::INVALID, '操作失败');
            }elseif(!$res){
                $this->autoAllot([\app\model\User::getLastInsID()]);
                continue;
            }
            $num++;
        }
        return $this->buildSuccess([], '操作成功共导入' . $num . '条');
    }

    public function setStatus(){
        $postData = $this->request->post();
        if(!isset($postData['userID']) || empty($postData['userID'])){
                return $this->buildFailed(ReturnCode::INVALID, '参数错误');
        }
        $userIDs = explode(',', $postData['userID']);
        $userIDs = array_filter($userIDs);
        $model = new model;
        if(false !== $model->where('userID','IN', $userIDs)->update(['status' => intval($postData['status'])])){
            return $this->buildSuccess([]);
        }
        return $this->buildFailed(ReturnCode::INVALID, '操作失败');
    }

    public function setCheck(){
        $postData = $this->request->post();
        if(!isset($postData['userID']) || empty($postData['userID'])){
                return $this->buildFailed(ReturnCode::INVALID, '参数错误');
        }
        if(!isset($postData['statusCheck']) || empty($postData['statusCheck'])){
            return $this->buildFailed(ReturnCode::INVALID, '参数错误');
        }

        Db::startTrans();
        try {
            $userIDs = explode(',', $postData['userID']);
            $userIDs = array_filter($userIDs);
            $model = new model;
            if(false !== $model->where('userID','IN', $userIDs)->update(['statusCheck' => intval($postData['statusCheck']), 'isFull' => 1, 'checkTime' => time(), 'checkManger' => $this->userInfo['id']])){
                //分配账号
                if(isset($postData['autoAllot']) && $postData['autoAllot'] && $postData['statusCheck'] == 1){
                    $this->autoAllot($userIDs);
                }

                //分配葵码+编号
                $this->kmUpdate($userIDs);

            }else{
                throw new Exception('操作失败');
            }

            Db::commit();
        }catch(\Exception $e){
            Db::rollback();
            return $this->buildFailed(ReturnCode::DB_SAVE_ERROR, $e->getMessage());
        }
        return $this->buildSuccess([]);


    }

    //分配葵码和编号
    private function kmUpdate($userIDs){

        $platformList = Db::name('platform')->select();
            $platform_abbr = [];
            foreach ($platformList as $item){
                $platform_abbr[$item['id']] = $item['abbr'];
            }
        foreach ($userIDs as $item){
            $user = Db::name('user')->where('userID', $item)->find();
            $where = [
                    'platformID' => ['EQ', $user['platformID']],
                    'newKm' => ['NEQ', ''],
                ];
               /* $max =  Db::name('user')->where($where)->order('number desc')->find();
                if(isset($max['userID'])){
                    $number = substr($max['number'], -5, 5);
                }else{
                    $number = '00000';
                }*/
                $kmCode = Db::name('km_code')->where('status', 2)->find();
                if(!isset($kmCode['id'])) throw new Exception('暂未可用葵码');
                if(!$user['platformID'])  throw new Exception('该用户无归属平台为空');
                $flag = Db::name('user')->where('userID', $user['userID'])
                    ->update([
                        'newKm' => $kmCode['code'],
//                        'number' => $platform_abbr[$user['platformID']].str_pad(Tools::bcCalculate($number,1, '+', 0),5,"0",STR_PAD_LEFT),
                        'number' => $platform_abbr[$user['platformID']].str_pad($kmCode['id'],5,"0",STR_PAD_LEFT),
                        'updataTime' => time()
                    ]);

                if(!$flag) throw new Exception('葵码提交失败【-1】');
                $flag = Db::name('km_code')->where('id', $kmCode['id'])->update([
                    'status' => 1,
                    'updateTime' => time()
                ]);
            if(!$flag) throw new Exception('葵码分配失败【-2】');
            }

        }


    public function autoAllot($userIDs){
         if(empty($userIDs)){
             return;
         }
         $operList = \app\model\FreshAdminUser::alias('u')
        ->field('u.id')
        ->join('fresh_admin_auth_group_access c', 'c.uid=u.id', 'left')
        ->join('fresh_admin_auth_group g', 'c.groupId=g.id', 'left')
        ->where('u.status', 1)
        ->where('g.isOp', 1)
        ->where('g.status', 1)
        ->select()
        ->toArray();
        $operIDs = $operList ? array_column($operList, 'id') : [];
        if(empty($operIDs)) return;
        $data = [];
        foreach($operIDs as $oper){
            $data[$oper] = \app\model\UserAllot::where('opMid', $oper)->where('status', '<>', 9)->count();
        }
        asort($data);
        $times = 0;
        while(!empty($userIDs) && $times < 50){
            foreach($data as $key =>$var){
                if($userIDs){
                    $res = \app\model\UserAllot::create([
                        'opMid' => $key,
                        'userID' => $userIDs[0],
                        'status' => 1,
                        'allotDate' => date("Y-m-d"),
                        'updateTime' => time(),
                        'addTime' => time(),
                        'serviceMid' => $this->userInfo['id']
                    ]);
                    if($res){
                        $falg = \app\model\User::where('userID', $userIDs[0])->update(['isAllot' => 1]);
                        if(!$falg) throw new Exception('分配失败');
                        array_shift($userIDs);
                    }
                }else{
                    break;
                }
            }
            $times++;
        }
    }

    public function listDownload(){
        $postData = $this->request->get();
        if(!isset($postData['acces_token']) || $postData['acces_token'] != 's4fnds5pms86rttb'){
            die('forbidden');
        }

        $postData = $this->request->get();
        $where = function($query)use($postData){
            if(isset($postData['status'])){
                $query->where('u.status', intval($postData['status']));
            }
            if(isset($postData['mobile'])&& $postData['mobile']){
                $query->where('u.mobile', $postData['mobile']);
            }
            if(isset($postData['keyword'])&& $postData['keyword']){
                $query->where('u.nickname|u.trueName', '%'.$postData['keyword'].'%');
            }
            if(isset($postData['xrkCode'])){
                $query->where('u.xrkCode', $postData['xrkCode']);
            }
            if(isset($postData['apliayUsername'])){
                $query->where('u.apliayUsername', $postData['apliayUsername']);
            }
            if(isset($postData['wechatUsername'])){
                $query->where('u.wechatUsername', $postData['wechatUsername']);
            }
            if(isset($postData['beginTime']) && $postData['beginTime']){
                $query->where('u.addTime', '>=', strtotime($postData['beginTime']));
            }
            if(isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('u.addTime', '<=', $endTime);
            }
            if(isset($postData['beginTime']) && $postData['beginTime'] && isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('u.addTime', [ '>=' , strtotime($postData['beginTime'])],['<=', $endTime], 'and');
            }

            $platformID = $this->userPlatform();
            if($platformID == 0){
                if(isset($postData['platformID'])   && $postData['platformID']){
                    $query->where('u.platformID', $postData['platformID']);
                }
            }else{
                $query->where('u.platformID', $platformID);
            }

        };
        $list = model::alias('u')
            ->with('platformData')
            ->where($where)
            ->order('u.addTime desc, u.userID desc')
            ->select();


        if($list){
            $list->append(['statusText']);
            $list->append(['checkStatusText']);
        }

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, '葵码编号');
        $worksheet->setCellValueByColumnAndRow(2, 1, '手机号');
        $worksheet->setCellValueByColumnAndRow(3, 1, '真实姓名');
        $worksheet->setCellValueByColumnAndRow(4, 1, '身份证号码');
        $worksheet->setCellValueByColumnAndRow(5, 1, '支付宝账号');
        $worksheet->setCellValueByColumnAndRow(6, 1, '登录密码');
        $worksheet->setCellValueByColumnAndRow(7, 1, '支付密码');
        $worksheet->setCellValueByColumnAndRow(8, 1, '微信账号');
        $worksheet->setCellValueByColumnAndRow(9, 1, '微信登录密码');
        $worksheet->setCellValueByColumnAndRow(10, 1, '微信支付密码');
        $worksheet->setCellValueByColumnAndRow(11, 1, '收款账号');
        $worksheet->setCellValueByColumnAndRow(12, 1, '收款真实姓名');
        $worksheet->setCellValueByColumnAndRow(13, 1, '收款类型');
        $worksheet->setCellValueByColumnAndRow(14, 1, '注册时间');
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(18);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(35);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $worksheet->getStyle('A1:N1')->applyFromArray($styleArray)->getFont()->setSize(14); //头部样式设置
        $len = count($list);
        $j = 0;
        for ($i = 0; $i < $len; $i++) {
            $j = $i + 2; //从表格第3行开始
            if($list[$i]['depositType'] == 1){
                $depositType = '支付宝';
            }else{
                $depositType = '微信';
            }
            $worksheet->setCellValueByColumnAndRow(1, $j, $list[$i]['xrkCode']);
            $worksheet->setCellValueByColumnAndRow(2, $j, $list[$i]['mobile']);
            $worksheet->setCellValueByColumnAndRow(3, $j, $list[$i]['trueName']);
            $worksheet->setCellValueByColumnAndRow(4, $j, $list[$i]['card']);
            $worksheet->setCellValueByColumnAndRow(5, $j, $list[$i]['apliayUsername']);
            $worksheet->setCellValueByColumnAndRow(6, $j, $list[$i]['apliayPassword']);
            $worksheet->setCellValueByColumnAndRow(7, $j, $list[$i]['apliayPayPassword']);
            $worksheet->setCellValueByColumnAndRow(8, $j, $list[$i]['wechatUsername']);
            $worksheet->setCellValueByColumnAndRow(9, $j, $list[$i]['wechatPassword']);
            $worksheet->setCellValueByColumnAndRow(10, $j, $list[$i]['wechatPayPassword']);
            $worksheet->setCellValueByColumnAndRow(11, $j, $list[$i]['depositUsername']);
            $worksheet->setCellValueByColumnAndRow(12, $j, $list[$i]['depositRealname']);
            $worksheet->setCellValueByColumnAndRow(13, $j, $depositType);
            $worksheet->setCellValueByColumnAndRow(14, $j,$list[$i]['addTime']);

        }
        if (!empty($postData['beginTime']) && $postData['beginTime'] && !empty($postData['endTime']) && $postData['endTime']) {
            $date = $postData['beginTime'] . " 至 " . $postData['endTime'] . ' 23:59:59';
            $worksheet->setCellValueByColumnAndRow(2, $j + 1, $date);
        }

        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $total_rows = $len + 2;
        //添加所有边框/居中
        $worksheet->getStyle('A1:N' . $total_rows)->applyFromArray($styleArrayBody);

        $filename = '用户资料.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

    }

    public function editLevel(){
        $postData = $this->request->post();
        if(!isset($postData['userID']) || empty($postData['userID'])){
                return $this->buildFailed(ReturnCode::INVALID, '参数错误');
        }
        $freshLevel = isset($postData['freshLevel']) ? intval($postData['freshLevel']) : 0;
        $model = new UserProgram;
        if(false !== $model->where('userID', $postData['userID'])->update(['freshLevel' => $freshLevel])){
            return $this->buildSuccess([]);
        }
        return $this->buildFailed(ReturnCode::INVALID, '操作失败');

    }


    public function awardList(){
        $page = $this->request->get('page', 1);
        $size = $this->request->get('size', config('apiAdmin.ADMIN_LIST_DEFAULT'));
        $postData = $this->request->get();
        $where = function($query)use($postData){
            if(isset($postData['orderSoruce'])){
                $query->where('w.orderSoruce', intval($postData['orderSoruce']));
            }
            if(isset($postData['type'])){
                $query->where('w.type', intval($postData['type']));
            }
            if(isset($postData['orderID'])){
                $query->where('w.orderID', intval($postData['orderID']));
            }
            if(isset($postData['mobile'])&& $postData['mobile']){
                $query->where('u.mobile', $postData['mobile']);
            }
            if(isset($postData['userID'])){
                $query->where('w.userID', intval($postData['userID']));
            }
            if(isset($postData['sourceUserID'])){
                $query->where('w.sourceUserID', intval($postData['sourceUserID']));
            }
            if(isset($postData['beginTime']) && $postData['beginTime']){
                $query->where('w.addTime', '>=', strtotime($postData['beginTime']));
            }
            if(isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('w.addTime', '<=', $endTime);
            }
            if(isset($postData['beginTime']) && $postData['beginTime'] && isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('w.addTime', [ '>=' , strtotime($postData['beginTime'])],['<=', $endTime], 'and');
            }

        };
        $list = FreshUserAward::alias('w')
        ->field('w.*,u.mobile,u.nickname,u.trueName')
        ->join('user u', 'u.userID=w.userID','LEFT')
        ->where($where)
        ->order('w.id desc')
        ->limit(( $page - 1 ) * $size , $size)
        ->select();
        if($list){
            $list->append(['statusText', 'radioText', 'orderSoruceText']);
        }

        foreach($list as $key => $var){
            $var->data('nickname', emoji_decode($var['nickname']));
            $var->data('trueName', $var['trueName'] ? : '');
        }

        $sumMoney = FreshUserAward::alias('w')
        ->join('user u', 'u.userID=w.userID','LEFT')
        ->where($where)
        ->sum("money");

        $count = FreshUserAward::alias('w')
        ->join('user u', 'u.userID=w.userID','LEFT')
        ->where($where)
        ->count();

        $sumMoney =Tools::bcCalculate($sumMoney, 100, '/');
        return $this->buildSuccess([
            'count'=>$count,
            'sumMoney'=>$sumMoney,
            'list' => $list
        ]);
    }

    public function moneyLog(){
        $page = $this->request->get('page', 1);
        $size = $this->request->get('size', config('apiAdmin.ADMIN_LIST_DEFAULT'));
        $postData = $this->request->get();

        $where = function ($query)use($postData){
            if(isset($postData['turnover']) && $postData['turnover']){
                $query->where('i.turnover', $postData['turnover']);
            }
            if(isset($postData['beginTime']) && $postData['beginTime']){
                $query->where('i.addtime', '>=', strtotime($postData['beginTime']));
            }
            if(isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('i.addtime', '<=', $endTime);
            }
            if(isset($postData['beginTime']) && $postData['beginTime'] && isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('i.addtime', [ '>=' , strtotime($postData['beginTime'])],['<=', $endTime], 'and');
            }
            if(isset($postData['mobile']) && $postData['mobile']){
                $query->where('u.mobile', $postData['mobile']);
            }
            if(isset($postData['userID']) && $postData['userID']){
                $query->where('i.userID', $postData['userID']);
            }
        };
        $list = FreshUserMoneyLog::alias('i')
        ->join('user u', 'u.userID=i.userID', 'LEFT')
        ->field('i.*,u.mobile as userMobile,u.nickname')
        ->where($where)
        ->order('addtime desc')
        ->limit(( $page - 1 ) * $size , $size)
        ->select();
        if($list){
            $list->append(['turnoverText','typeText', 'orderSoruceText']);
        }
        foreach($list as $key => $var){
            $var->data('nickname', emoji_decode($var['nickname']));
        }
        $count = FreshUserMoneyLog::alias('i')->join('user u', 'u.userID=i.userID', 'LEFT')->where($where)->count();
        $income = FreshUserMoneyLog::alias('i')->join('user u', 'u.userID=i.userID', 'LEFT')->where($where)->where('turnover', 1)->sum('money');
        $expend = FreshUserMoneyLog::alias('i')->join('user u', 'u.userID=i.userID', 'LEFT')->where($where)->where('turnover', -1)->sum('money');
        $expend =Tools::bcCalculate($expend, 100, '/');
        $income =Tools::bcCalculate($income, 100, '/');
        return $this->buildSuccess([
            'count'=>$count,
            'list'=>$list,
            'sumIncome'=>$income,
            'sumExpend'=>$expend
        ]);
    }

    public function protocol(){
        $data = Db::table('a_fresh_config')->field('value')->where(['group' => 'DIAL', 'name'=>'PROTOCOL_USER'])->find();
        return $this->buildSuccess($data);
    }

    public function editProtocol(){
        $postData = $this->request->post();
        $res = Db::table('a_fresh_config')->where(['group' => 'DIAL', 'name'=>'PROTOCOL_USER'])->update(['value' => $postData['PROTOCOL_USER'], 'update_time' => date("Y-m-d H:i:s")]);
        if($res){
            return $this->buildSuccess([]);
        }else{
            return $this->buildFailed(-1,'修改失败');
        }
    }

    public function privacy(){
        $data = Db::table('a_fresh_config')->field('value')->where(['group' => 'DIAL', 'name'=>'PRIVACY_USER'])->find();
        return $this->buildSuccess($data);
    }

    public function editPrivacy(){
        $postData = $this->request->post();
        $res = Db::table('a_fresh_config')->where(['group' => 'DIAL', 'name'=>'PRIVACY_USER'])->update(['value' => $postData['PRIVACY_USER'], 'update_time' => date("Y-m-d H:i:s")]);
        if($res){
            return $this->buildSuccess([]);
        }else{
            return $this->buildFailed(-1,'修改失败');
        }
    }

    public function edit(){


        $postData = $this->request->post();
        if(!isset($postData['userID']))  return $this->buildFailed(ReturnCode::INVALID, '用户ID不能为空');
        $user = \app\model\User::get(['userID' => $postData['userID']]);
        if(!isset($user['userID'])) return $this->buildFailed(ReturnCode::INVALID, '用户不存在');

        if(isset($postData['card'])){
            $result = \app\model\User::get(['card' => $postData['card']]);
            if(isset($result['userID']) && $result['userID'] != $user['userID']) return $this->buildFailed(ReturnCode::INVALID, '该身份证号码已注册');
        }

        if(isset($postData['apliayUsername'])){
            $result = \app\model\User::get(['apliayUsername' => $postData['apliayUsername']]);
            if(isset($result['userID']) && $result['userID'] != $user['userID']) return $this->buildFailed(ReturnCode::INVALID, '该支付宝账号已注册');
        }

        if(isset($postData['wechatUsername'])){
            $result = \app\model\User::get(['wechatUsername' => $postData['wechatUsername']]);
            if(isset($result['userID']) && $result['userID'] != $user['userID']) return $this->buildFailed(ReturnCode::INVALID, '该微信账号已注册');
        }


        if(isset($postData['xrkCode'])){
            $result = \app\model\User::get(['xrkCode' => $postData['xrkCode']]);
            if(isset($result['userID']) && $result['userID'] != $user['userID']) return $this->buildFailed(ReturnCode::INVALID, '该葵码已绑定');
        }
        $model = new \app\model\User();
        unset($postData['addTime']);
        unset($postData['checkTime']);
        $postData['updataTime'] = time();
        $res = $model->strict(false)->update($postData, ['userID' => $user['userID']]);
        if ($res === false) {
            return $this->buildFailed(ReturnCode::DB_SAVE_ERROR, '操作失败');
        } else {

            return $this->buildSuccess([]);
        }

    }

    public function userSearch(){

        $postData = $this->request->get();
        if(empty($postData['xrkCode']))  return $this->buildFailed(ReturnCode::INVALID, '葵码参数不能为空');
//        $data = Db::name('user')->where('xrkCode', $postData['xrkCode'])->find();
        $model = new \app\model\User();
        $data = $model->where(['xrkCode' => $postData['xrkCode']])->select();
        $data->append(['statusText']);
        $data->append(['checkStatusText']);
        return $this->buildSuccess([
            'list'=>$data,
            'count'=>count($data),
        ]);
    }


    public function allot(){
        $postData = $this->request->post();
        if(!isset($postData['userID']) || empty($postData['userID']))  return $this->buildFailed(ReturnCode::INVALID, 'userID不能为空');
        if(!isset($postData['opMid']) || empty($postData['opMid']))  return $this->buildFailed(ReturnCode::INVALID, 'opMid不能为空');
        Db::startTrans();
        try{
            $user = model::where('userID', $postData['userID'])->lock(true)->find();
            if(empty($user)){
                throw new \Exception('用户不存在');
            }
            if($user['statusCheck'] != 1){
                throw new \Exception('审核通过用户才能进行绑定');
            }
            if($user['isAllot'] == 1){
                throw new \Exception('用户已经绑定不能再次绑定');
            }
            if($user['isAllot'] == 2){
                throw new \Exception('用户异常请先处理异常');
            }
            $res = model::where('userID', $postData['userID'])->update(['isAllot' => 1, 'updataTime' => time()]);
            if(!$res){
                throw new \Exception('更新用户信息失败');
            }
            $oprUser =  \app\model\FreshAdminAuthGroupAccess::alias('a')
            ->field('a.uid,a.groupId,g.platformID,g.isOp')
            ->join('fresh_admin_auth_group g', 'a.groupId=g.id', 'left')
            ->where('a.uid', $postData['opMid'])
            ->find();
            if($oprUser['isOp'] != 1){
                throw new \Exception('非法操作员');
            }
            $platformID = $this->userPlatform();
            if($platformID && $oprUser['platformID'] != $platformID){
                throw new \Exception('不能为其他平台操作员绑定用户');
            }
            $data = [
                'opMid' => $postData['opMid'],
                'userID' => $postData['userID'],
                'status' => 1,
                'allotDate' => date("Y-m-d"),
                'serviceMid' => $this->userInfo['id']
            ];
            $res = \app\model\UserAllot::create($data);
            if(!$res){
                throw new \Exception('绑定失败');
            }
            Db::commit();
            return $this->buildSuccess([]);
        }catch(\Exception $e){
            Db::rollback();
            return $this->buildFailed(ReturnCode::DB_SAVE_ERROR, $e->getMessage());
        }
    }


    public function cancelAllot(){
        $postData = $this->request->post();
        if(!isset($postData['id']) || empty($postData['id']))  return $this->buildFailed(ReturnCode::INVALID, 'id不能为空');
        Db::startTrans();
        try{
            $record = \app\model\UserAllot::get($postData['id']);
            if(empty($record)){
                throw new \Exception('绑定记录不存在');
            }
            $oprUser =  \app\model\FreshAdminAuthGroupAccess::alias('a')
            ->field('a.uid,a.groupId,g.platformID,g.isOp')
            ->join('fresh_admin_auth_group g', 'a.groupId=g.id', 'left')
            ->where('a.uid', $record->opMid)
            ->find();
            $platformID = $this->userPlatform();
            if($platformID && $oprUser['isOp'] == 1 && $oprUser['platformID'] != $platformID){
                throw new \Exception('不能操作其他平台操作员');
            }
            $res = model::where('userID', $record->userID)->update(['isAllot' => 9, 'updataTime' => time()]);
            if(!$res){
                throw new \Exception('更新用户信息失败');
            }
            $record->status = 9;
            $record->serviceMid =  $this->userInfo['id'];
            if(!$record->save()){
                throw new \Exception('解除绑定失败');
            }
            Db::commit();
            return $this->buildSuccess([]);
        }catch(\Exception $e){
            Db::rollback();
            return $this->buildFailed(ReturnCode::DB_SAVE_ERROR, $e->getMessage());
        }
    }

    public function allotList(){
        $page = $this->request->get('page', 1);
        $size = $this->request->get('size', config('apiAdmin.ADMIN_LIST_DEFAULT'));
        $postData = $this->request->get();
        $where = function($query)use($postData){
            if(isset($postData['keyword'])&& $postData['keyword']){
                $query->where('au.username|au.nickname|u.nickname|u.trueName',  'like','%'.$postData['keyword'].'%');
            }
            if(isset($postData['status'])){
                $query->where('a.status', intval($postData['status']));
            }else{
                $query->where('a.status', '<>', 9);
            }
            if(isset($postData['xrkCode'])){
                $query->where('u.xrkCode', $postData['xrkCode']);
            }
            if(isset($postData['mobile'])){
                $query->where('u.mobile', $postData['mobile']);
            }
            if(isset($postData['keywordMobile'])&& $postData['keywordMobile']){
                $query->where('u.mobile',  'like','%'.$postData['keywordMobile'].'%');
            }
            if(isset($postData['opMid'])){
                $query->where('a.opMid', intval($postData['opMid']));
            }
            if(isset($postData['userID'])){
                $query->where('a.userID', intval($postData['userID']));
            }

            if(isset($postData['beginTime']) && $postData['beginTime']){
                $query->where('a.addTime', '>=', strtotime($postData['beginTime']));
            }
            if(isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('a.addTime', '<=', $endTime);
            }
            if(isset($postData['beginTime']) && $postData['beginTime'] && isset($postData['endTime']) && $postData['endTime']){
                $endTime = strtotime($postData['endTime']) + 3600*24 -1;
                $query->where('a.addTime', [ '>=' , strtotime($postData['beginTime'])],['<=', $endTime], 'and');
            }

        };
        $groupWhere = [];
       /* $platformID = $this->userPlatform();
        if($platformID){
            $groupWhere['g.platformID'] = $platformID;
        }*/

        $platformID = $this->userPlatform();

        if ($platformID) {
            $groupWhere['g.platformID'] = $platformID;
        }else{
            if(isset($postData['platformID'])  && $postData['platformID']){
                $groupWhere['g.platformID'] =  $postData['platformID'];
            }
        }
//        var_dump($groupWhere);exit();

        $list = \app\model\UserAllot::alias('a')
        ->field('a.*,u.mobile,u.photo,u.xrkCode,u.nickname as userNickName,u.trueName as userTrueName,p.name as platformName,au.username as opUserName,au.nickname as opNickname,auc.username as aucUserName,auc.nickname as aucNickname,
        u.*')
        ->join('fresh_admin_user au', 'a.opMid=au.id', 'left')
        ->join('user u', 'a.userID=u.userID', 'left')
        ->join('fresh_admin_user auc', 'a.serviceMid=auc.id', 'left')
        ->join('fresh_admin_auth_group_access c', 'c.uid=au.id', 'left')
        ->join('fresh_admin_auth_group g', 'c.groupId=g.id', 'left')
        ->join('platform p', 'p.id=g.platformID', 'left')
        ->where($where)
        ->where($groupWhere)
        ->limit(( $page - 1 ) * $size , $size)
        ->select();

        if($list){
            $list->append(['statusText']);
        }

        foreach($list as $key => $var){
            $var->data('userNickName', $var['userNickName'] ? emoji_decode($var['userNickName']): '');
        }
        $count = \app\model\UserAllot::alias('a')
        ->field('a.*,u.photo,u.xrkCode,u.nickname as userNickName,p.name as platformName,au.username as opUserName,au.nickname as opNickname,auc.username as aucUserName,auc.nickname as aucNickname,')
        ->join('fresh_admin_user au', 'a.opMid=au.id', 'left')
        ->join('user u', 'a.userID=u.userID', 'left')
        ->join('fresh_admin_user auc', 'a.serviceMid=au.id', 'left')
        ->join('fresh_admin_auth_group_access c', 'c.uid=au.id', 'left')
        ->join('fresh_admin_auth_group g', 'c.groupId=g.id', 'left')
        ->join('platform p', 'p.id=g.platformID', 'left')
        ->where($where)
        ->where($groupWhere)
        ->count();

        return $this->buildSuccess([
            'count'=>$count,
            'list'=>$list
        ]);
    }

    public function updateNewKm(){

        $param =  $this->request->post();
        if(!isset($param['content']))return $this->buildFailed(ReturnCode::INVALID, '新葵码不能为空');
        $where = ['statusCheck' => 1,'newKm' => ''];


        Db::startTrans();
        try{
            $user_list = Db::name('user')->where($where)->select();
            if(count($user_list) == 0) throw new Exception('未找到分配的用户');

            $platformList = Db::name('platform')->select();
            $platform_abbr = [];
            foreach ($platformList as $item){
                $platform_abbr[$item['id']] = $item['abbr'];
            }

            $content_arr = preg_split("/(,|，)/",$param['content']);
            foreach ($user_list as $item){
                $where = [
                    'platformID' => ['EQ', $item['platformID']],
                    'newKm' => ['NEQ', ''],
                ];
                $max =  Db::name('user')->where($where)->order('number desc')->find();
                if(isset($max['userID'])){
                    $number = $platform_abbr[$item['platformID']].substr($max['number'], -5, 5);
                }else{
                    $number = $platform_abbr[$item['platformID']].'00000';
                }

                $flag = Db::name('user')->where('userID', $item['userID'])
                    ->update([
                        'newKm' => array_pop($content_arr),
                        'number' => str_pad(Tools::bcCalculate($number,1, '+'),5,"0",STR_PAD_LEFT),
                        'updataTime' => time()
                    ]);
                if(!$flag) throw new Exception('葵码提交失败!');
            }
            Db::commit();
            return $this->buildSuccess($content_arr);
        }catch(\Exception $e){
            Db::rollback();
            return $this->buildFailed(ReturnCode::DB_SAVE_ERROR, $e->getMessage());
        }
    }

    //用户分配操作员
    public function backageAllot(){

        echo "####start####" . date("Y-m-d H:i:s") . "\n\n";

        Db::startTrans();
        try {

            $where = ['isAllot' => 9, 'statusCheck' => 1, 'newKm' => '', 'userID' => 258];
            $userIDs = Db::name('user')->where($where)->column('userID');
//            var_dump($userIDs);exit();
            //分配账号
            $this->autoAllot($userIDs);
            //分配葵码+编号
            $this->kmUpdate($userIDs);
            Db::commit();
        } catch (Exception $e) {
            echo $e->getMessage()."\n";
            Db::rollback();
        }
        echo "####end####" . date("Y-m-d H:i:s");
    }

}

?>
